package de.fhhof.vinf.ssp.ticketcenter.beans;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.naming.NamingException;

import de.fhhof.vinf.ssp.ticketcenter.helpers.DatabaseManager;

public class TicketRequestBean {
	
	private Integer ticketRequestId;			// ticket_request_id
	private Integer isGranted;					// is_granted
	private Integer requestedTickets;			// requested_tickets
	private Integer userId;						// user_id
	private Integer ticketCategoryId;			// ticket_category_id
	
	private UserBean userBean;
	private TicketCategoryBean ticketCatBean;
	public Integer getTicketRequestId() {
		return ticketRequestId;
	}
	public void setTicketRequestId(Integer ticketRequestId) {
		this.ticketRequestId = ticketRequestId;
	}
	public Integer getIsGranted() {
		return isGranted;
	}
	public void setIsGranted(Integer isGranted) {
		this.isGranted = isGranted;
	}
	public Integer getRequestedTickets() {
		return requestedTickets;
	}
	public void setRequestedTickets(Integer requestedTickets) {
		this.requestedTickets = requestedTickets;
	}
	public Integer getUserId() {
		return userId;
	}
	public void setUserId(Integer userId) {
		this.userId = userId;
	}
	public Integer getTicketCategoryId() {
		return ticketCategoryId;
	}
	public void setTicketCategoryId(Integer ticketCategoryId) {
		this.ticketCategoryId = ticketCategoryId;
	}
	public UserBean getUserBean() {
		return userBean;
	}
	public void setUserBean(UserBean userBean) {
		this.userBean = userBean;
	}
	public TicketCategoryBean getTicketCatBean() {
		return ticketCatBean;
	}
	public void setTicketCatBean(TicketCategoryBean ticketCatBean) {
		this.ticketCatBean = ticketCatBean;
	}
	
	/**
	 * Liest einen Datensatz aus der Tabelle und speichert diese in der Bean.
	 * @param ticketRequestId	ID des TicketRequest-Datensatzes, der eingelesen werden soll
	 * @throws SQLException
	 * @throws NamingException
	 * @throws ClassNotFoundException
	 */
	public void readFromDB(Integer ticketRequestId) throws SQLException, NamingException, ClassNotFoundException{
		Connection con = DatabaseManager.getConnection();
		Statement stmt = con.createStatement();
		ResultSet res = stmt.executeQuery("SELECT * FROM ticket_requests WHERE ticket_request_id=" + ticketRequestId + ";");
		while (res.next()) {
			this.setTicketRequestId(ticketRequestId);
			this.setIsGranted(res.getInt("is_granted"));
			this.setRequestedTickets(res.getInt("requested_tickets"));
			this.setUserId(res.getInt("user_id"));
			this.setTicketCategoryId(res.getInt("ticket_category_id"));			
		}
		// Beans einlesen
		TicketCategoryBean ticketCatBean = new TicketCategoryBean();
		ticketCatBean.readFromDB(this.getTicketCategoryId());
		UserBean userBean = new UserBean();
		userBean.readFromDB(this.getUserId());
		
		this.setTicketCatBean(ticketCatBean);
		this.setUserBean(userBean);
		
		con.close();
	}
	
	/**
	 * Schreibt die Daten der Bean in die Tabelle ticket_requests.
	 * @throws SQLException 
	 * @throws NamingException 
	 */
	public void writeToDB() throws SQLException, NamingException {
		Connection con = DatabaseManager.getConnection();
		Statement stmt = con.createStatement();
		stmt.executeUpdate("INSERT INTO ticket_requests (is_granted, requested_tickets, user_id, ticket_category_id)" +
				" VALUES (" + this.getIsGranted() + "," + this.getRequestedTickets() + "," + this.getUserId() + "," + this.getTicketCategoryId() + ");");
		con.close();
	}

}
